import pool from '../database';
import type {BusData, HotelData} from '../../types/DataTypes';

export class HotelDAO {
    // 新增酒店
    static async addHotel(hotel: HotelData): Promise<boolean> {
        const connection = await pool.getConnection();
        try {
            const [result] = await connection.execute(
                `INSERT INTO Hotel (location, price, totalRoom, availRoom) 
                 VALUES (?, ?, ?, ?)`,
                [hotel.location, hotel.price, hotel.totalRoom, hotel.availRoom]
            );
            return (result as any).affectedRows > 0;
        } catch (error: any) {
            if (error.code === 'ER_DUP_ENTRY') {
                throw new Error(`酒店位置 ${hotel.location} 已存在`);
            }
            throw error;
        } finally {
            connection.release();
        }
    }

    // 获取所有酒店
    static async getAllHotels(): Promise<HotelData[]> {
        const connection = await pool.getConnection();
        try {
            const [rows] = await connection.execute(
                'SELECT * FROM Hotel ORDER BY location'
            );
            return rows as HotelData[];
        } finally {
            connection.release();
        }
    }

    // 根据位置模糊查询酒店
 /*   static async searchHotelsByLocation(location: string): Promise<HotelData[]> {
        const connection = await pool.getConnection();
        try {
            const [rows] = await connection.execute(
                'SELECT * FROM Hotel WHERE location LIKE ? ORDER BY location',
                [`%${location}%`]
            );
            return rows as HotelData[];
        } finally {
            connection.release();
        }
    }

    // 根据价格范围查询酒店
    static async searchHotelsByPriceRange(minPrice: number, maxPrice: number): Promise<HotelData[]> {
        const connection = await pool.getConnection();
        try {
            const [rows] = await connection.execute(
                'SELECT * FROM Hotel WHERE price BETWEEN ? AND ? ORDER BY price, location',
                [minPrice, maxPrice]
            );
            return rows as HotelData[];
        } finally {
            connection.release();
        }
    }*/

    // 综合搜索（位置和价格范围）
    static async advancedSearch(criteria: {
        location?: string;
        minPrice?: number;
        maxPrice?: number;
    }): Promise<HotelData[]> {
        const connection = await pool.getConnection();
        try {
            let query = 'SELECT * FROM Hotel WHERE 1=1';
            const params: any[] = [];

            if (criteria.location) {
                query += ' AND location LIKE ?';
                params.push(`%${criteria.location}%`);
            }

            if (criteria.minPrice !== undefined) {
                query += ' AND price >= ?';
                params.push(criteria.minPrice);
            }

            if (criteria.maxPrice !== undefined) {
                query += ' AND price <= ?';
                params.push(criteria.maxPrice);
            }

            query += ' ORDER BY location';

            const [rows] = await connection.execute(query, params);
            return rows as HotelData[];
        } finally {
            connection.release();
        }
    }

    // 更新酒店数据
    static async updateHotel(hotelData: HotelData): Promise<boolean> {
        const connection = await pool.getConnection();
        try {
            const [result] = await connection.execute(
                `UPDATE Hotel SET 
                 price = ?, 
                 totalRoom = ?, 
                 availRoom = ?
                 WHERE location = ?`,
                [hotelData.price, hotelData.totalRoom, hotelData.availRoom, hotelData.location]
            );

            return (result as any).affectedRows > 0;
        } finally {
            connection.release();
        }
    }

    // 可用房间数量增加
    static async incrementHotelAvailability(location: string): Promise<boolean> {
        const connection = await pool.getConnection();
        try {
            const [result] = await connection.execute(
                `UPDATE Hotel 
                 SET availRoom = availRoom + 1 
                 WHERE location = ? 
                 AND availRoom < totalRoom`,
                [location]
            );
            return (result as any).affectedRows > 0;
        } finally {
            connection.release();
        }
    }

    // 可用房间数量减少
    static async decrementHotelAvailability(location: string): Promise<boolean> {
        const connection = await pool.getConnection();
        try {
            const [result] = await connection.execute(
                `UPDATE Hotel 
                 SET availRoom = availRoom - 1 
                 WHERE location = ? 
                 AND availRoom > 0`,
                [location]
            );
            return (result as any).affectedRows > 0;
        } finally {
            connection.release();
        }
    }

    // 删除酒店
    static async deleteHotel(location: string): Promise<boolean> {
        const connection = await pool.getConnection();
        try {
            const [result] = await connection.execute(
                'DELETE FROM Hotel WHERE location = ?',
                [location]
            );
            return (result as any).affectedRows > 0;
        } finally {
            connection.release();
        }
    }

    // 检查酒店是否存在
    static async hotelExists(location: string): Promise<boolean> {
        const connection = await pool.getConnection();
        try {
            const [rows] = await connection.execute(
                'SELECT 1 FROM Hotel WHERE location = ?',
                [location]
            );
            return (rows as any[]).length > 0;
        } finally {
            connection.release();
        }
    }

    // 根据精确信息查询酒店
    static async getHotelByLocation(location: string): Promise<HotelData | null> {
        const connection = await pool.getConnection();
        try {
            const [rows] = await connection.execute(
                'SELECT * FROM Hotel WHERE location = ?',
                [location]
            );
            const hotel = rows as HotelData[];
            return hotel.length > 0 ? hotel[0] : null;
        } finally {
            connection.release();
        }
    }
}